package com.itheima.dao.order;


import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.sql.Date;
import java.util.List;
import java.util.Map;

/**
 * 运营数据统计页面【预约数】的相关查询
 * @Author: 杨雅雯
 * @Date: 2021/11/4 16:51
 */
public interface OrderDao {
    /**
     * 根据日期统计预约数
     * @param orderDate 日期
     * @return 返回今日预约数
     */
    @Select("SELECT COUNT(id) FROM t_order WHERE orderDate = #{orderDate}")
    public Integer findOrderCountByDate(@Param("orderDate") Date orderDate);


    /**
     * 根据日期统计预约数，统计指定日期之后的预约数
     * @param orderDate 日期
     * @return 返回数值
     */
    @Select("SELECT COUNT(id) FROM t_order WHERE orderDate > #{orderDate}")
    public Integer findOrderCountAfterDate(@Param("orderDate")Date orderDate);


    /**
     * 根据日期统计到诊数
     * @param orderStatus 到诊情况
     * @return 返回今日到诊数
     */
    @Select("SELECT COUNT(id) FROM t_order WHERE orderDate = #{orderDate} and orderStatus ='已到诊'")
    public Integer findVisitsCountByDate(@Param("orderDate") Date orderDate,@Param("orderStatus") String orderStatus);

    /**
     * 根据日期统计到诊数，统计指定日期之后的到诊数
     * @param orderDate 日期
     * @return 返回数值
     */
    @Select("SELECT COUNT(id) FROM t_order WHERE orderDate > #{orderDate} and orderStatus ='已到诊'")
    public Integer findVisitsCountAfterDate(@Param("orderDate") Date orderDate,@Param("orderStatus") String orderStatus);


    /**
     * 热门套餐
     * @return 返回前四条
     */
    @Select("SELECT\n" +
            "\ts.name,\n" +
            "\tCOUNT(o.id) setmeal_count,\n" +
            "\tCOUNT(o.id)/(SELECT COUNT(id) FROM t_order) proportion\n" +
            "FROM\n" +
            "\tt_order o\n" +
            "LEFT JOIN\n" +
            "\tt_setmeal s\n" +
            "ON\n" +
            "\ts.id=o.setmeal_id\n" +
            "GROUP BY o.setmeal_id\n" +
            "ORDER BY setmeal_count DESC\n" +
            "LIMIT 0,4")
    public List<Map> findHotSetmeal();
}
